package com.example.yuntiku.dao.impl;

import com.example.yuntiku.Vo.PagerVo;
import com.example.yuntiku.dao.BaseDao;
import com.example.yuntiku.dao.PagerDao;
import com.example.yuntiku.entity.Pager;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class PagerDaoImpl extends BaseDao implements PagerDao {

    @Override
    public List<PagerVo> selectPager(String pname, String ptime, int page, int size) {
        List<PagerVo> list=new ArrayList<>();
        List<Object> params=new ArrayList<>();
        //动态拼接sql语句
        String sql="SELECT a.pname,duration,sumscore,b.tname,ptime FROM `pager`a\n" +
                "JOIN `teacher` b ON a.teacherid=b.tid\n" +
                "JOIN `product` c ON a.productid=c.pid\n" +
                "WHERE 1=1";
        //判断条件是否为空
        if(pname!=null && !pname.equals("")){
            sql+=" AND a.pname like ?";
            params.add(pname);//有参数加入到集合
        }
        if(ptime!=null && !ptime.equals("")){
            sql+=" AND ptime LIKE CONCAT(?,'%')";
            params.add(ptime);//有参数加入到集合
        }
        //拼接group by
        sql+=" order by ptime desc limit ?,?";
        params.add((page-1)*size);
        params.add(size);
        Connection conn = null;
        //定义语句对象，执行sql语句
        PreparedStatement stmt = null;
        //加载驱动类
        ResultSet rs = null;
        try {
            conn = super.getConnection();
            stmt = conn.prepareStatement(sql);
            //循环添加参数
            for (int i = 0; i <params.size() ; i++) {
                stmt.setObject(i+1,params.get(i));
            }
            rs = stmt.executeQuery();
            while (rs.next()) {
                PagerVo entity = new PagerVo();
                entity.setPname(rs.getString("pname"));
                entity.setDuration(rs.getString("duration"));
                entity.setSumscore(rs.getDouble("sumscore"));
                entity.setTeacher(rs.getString("tname"));
                entity.setPtime(rs.getString("ptime"));
                list.add(entity);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            super.closeAll(conn, stmt, rs);
        }
        return list;
    }

    @Override
    public int selectcount(String pname, String ptime) {
        List<Object> params = new ArrayList<>();
        String sql = "SELECT COUNT(1) FROM `pager` a\n" +
                "JOIN `teacher` b ON a.teacherid=b.tid\n" +
                "JOIN `product` c ON a.productid=c.pid\n" +
                "WHERE 1=1";
        //根据判断添加参数
        if(pname !=null && !pname.equals("")){
            sql+=" AND a.pname=?";
            params.add(pname);
        }
        if(ptime !=null && !ptime.equals("")){
            sql += " AND ptime like CONCAT('%',?,'%')";
            params.add(ptime);
        }
        return super.count(sql,params.toArray());
    }


    @Override
    public List<Pager> selectAll() {
        List<Pager> list=new ArrayList<>();
        String sql="SELECT rid, pname, duration, tote, sumscore, score, productid, teacherid, ptime, ptype \n" +
                "FROM `pager` \n" +
                "ORDER BY ptime DESC;\n";
        ResultSet rs=null;
        try {
            rs=super.executeQuery(sql);
            while (rs.next()) { //一次读取一行记录，读完返回false
                //转换成实体类对象，一个对象，装一条数据
                Pager entity = new Pager();
                entity.setRid(rs.getInt("rid"));
                entity.setPname(rs.getString("pname"));
                entity.setDuration(rs.getString("duration"));
                entity.setTote(rs.getInt("tote"));
                entity.setSumscore(rs.getDouble("sumscore"));
                entity.setScore(rs.getDouble("score"));
                entity.setProductid(rs.getInt("productid"));
                entity.setTeacherid(rs.getInt("teacherid"));
                entity.setPtime(rs.getString("ptime"));
                entity.setPtype(rs.getInt("ptype"));
                //添加到集合
                list.add(entity);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            super.closeAll(conn,null,rs);
        }
        return list;
    }

    @Override
    public int pagerinsert(Pager pager){
        String sql="INSERT INTO `yuntiku`.`pager` (`pname`,`duration`,`tote`,`sumscore`,`score`,`productid`,`teacherid`,`ptime`,`ptype`) \n" +
                "VALUES(?,?,?,?,?,?,?,NOW(),?);";
        return super.insertForId(sql,pager.getPname(),pager.getDuration(),pager.getTote(),
                pager.getSumscore(),pager.getScore(),pager.getProductid(),pager.getTeacherid(),pager.getPtype());
    }

    @Override
    public Pager selectByExam(int examId) {
        String sql="SELECT rid,pname, duration, tote, sumscore, score, productid, p.teacherid, ptime, p.ptype\n" +
                "FROM `pager` p\n" +
                "JOIN `exam` e ON p.`rid`=e.`pagerid`\n" +
                "WHERE e.`id`=?;";
        ResultSet rs=null;
        try {
            rs=super.executeQuery(sql,examId);
            while (rs.next()) { //一次读取一行记录，读完返回false
                //转换成实体类对象，一个对象，装一条数据
                Pager entity = new Pager();
                entity.setRid(rs.getInt("rid"));
                entity.setPname(rs.getString("pname"));
                entity.setDuration(rs.getString("duration"));
                entity.setTote(rs.getInt("tote"));
                entity.setSumscore(rs.getDouble("sumscore"));
                entity.setScore(rs.getDouble("score"));
                entity.setProductid(rs.getInt("productid"));
                entity.setTeacherid(rs.getInt("teacherid"));
                entity.setPtime(rs.getString("ptime"));
                entity.setPtype(rs.getInt("ptype"));
                return entity;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            super.closeAll(conn,null,rs);
        }
        return null;
    }

    @Override
    public Pager selectById(int id) {
        String sql="SELECT `rid`,`pname`,`duration`,`tote`,`ptype`\n" +
                "FROM `pager`\n" +
                "WHERE `rid`=?";

        ResultSet rs=null;
        try {
            rs=super.executeQuery(sql,id);
            while (rs.next()) { //一次读取一行记录，读完返回false
                //转换成实体类对象，一个对象，装一条数据
                Pager entity = new Pager();
                entity.setRid(rs.getInt("rid"));
                entity.setPname(rs.getString("pname"));
                entity.setDuration(rs.getString("duration"));
                entity.setTote(rs.getInt("tote"));
                entity.setPtype(rs.getInt("ptype"));
                return entity;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            super.closeAll(conn,null,rs);
        }
        return null;
    }
}
